package db_sports

import (
	"errors"
	"fmt"
	"gorm.io/gorm"
	"longmen/server/config/global"
	"longmen/server/pkg/common/helper/const"
	"longmen/server/pkg/common/helper/timehelper"
	"longmen/server/pkg/common/helper/util"
	"longmen/server/pkg/db/mysql/models/models_main"
	pb "longmen/server/pkg/protobuf"
	"strings"
)

/**
* @Author:admin
* @Description: 返回一个数组
* @Param :
* @Return : list
* @Date: 16:01 2022/6/17
 */
func GetExpertPlanListByParam(expertPlanParam *pb.ExpertPlanListReq) ([]*pb.ItemExpertPlan, error) {
	e := make([]*pb.ItemExpertPlan, 0)
	condition := _const.EMPTY_STR
	//注意此项不是专家ID,当选了此有料id之后，查询出除此有料列表
	if expertPlanParam.ExpertId > 0 {
		condition = fmt.Sprintf("and tb1.id<>%d", expertPlanParam.ExpertId)
	}
	if expertPlanParam.SportId > 0 {
		condition = fmt.Sprintf("%s and tb1.sport_id=%d", condition, expertPlanParam.SportId)
	}
	if expertPlanParam.MatchId > 0 {
		condition = fmt.Sprintf("%s and tb1.match_id=%d", condition, expertPlanParam.MatchId)
	}
	if expertPlanParam.InfoExpertId > 0 {
		condition = fmt.Sprintf("%s and tb3.id=%d", condition, expertPlanParam.InfoExpertId)
	}
	if len(expertPlanParam.PlayTypes) > 0 {
		condition = fmt.Sprintf("%s and tb1.play_type in(%s)", condition, util.QuerySlice32Str(expertPlanParam.PlayTypes))
	}
	if len(expertPlanParam.FeeTypes) > 0 {
		condition = fmt.Sprintf("%s and tb1.fee_type in(%s)", condition, util.QuerySlice32Str(expertPlanParam.FeeTypes))
	}
	if len(expertPlanParam.Comps) > 0 {
		condition = fmt.Sprintf("%s and tb1.comp in(%s)", condition, util.QueryStrSliceToStr(expertPlanParam.Comps))
	}

	sql := fmt.Sprintf("select tb1.id,tb1.expert_id,tb1.play_type,tb1.title,tb1.fee_type,tb1.fee_pay,tb1.content,tb1.is_win,tb1.home_score plan_home_score,tb1.away_score "+
		"plan_away_score,tb1.create_time,tb1.match_id,tb3.`profile`,tb3.nick_name,tb3.is_logicians,case when tb3.plan_done_count>=10 then tb3.win_rate*10 else round(tb3.win_rate/tb3.plan_done_count*100,0) end win_rate,tb3.grade,tb3.resume from db_expert_plan tb1,db_expert tb3 where tb1.expert_id = tb3.id  "+
		"%s order by tb1.id desc LIMIT ? OFFSET ?", condition)
	if len(expertPlanParam.MatchName) > 0 {
		sql = fmt.Sprintf("select tb1.id,tb1.expert_id,tb1.play_type,tb1.title,tb1.fee_type,tb1.fee_pay,tb1.content,tb1.is_win,tb1.home_score plan_home_score,tb1.away_score "+
			"plan_away_score,tb1.create_time,tb1.match_id,tb3.`profile`,tb3.nick_name,tb3.is_logicians,case when tb3.plan_done_count>=10 then tb3.win_rate*10 else round(tb3.win_rate/tb3.plan_done_count*100,0) end win_rate,tb3.grade,tb3.resume from main.db_expert_plan tb1,"+
			"main.db_expert tb3,models_sports.db_matches_schedule tb4 where tb1.expert_id = tb3.id and tb1.match_id=tb4.match_id  and (home='%s' or away='%s')"+
			" order by tb1.id desc LIMIT ? OFFSET ?", expertPlanParam.MatchName, expertPlanParam.MatchName)
	}
	table := global.GetR().Raw(sql, expertPlanParam.Size, (expertPlanParam.Page-1)*expertPlanParam.Size)
	err := table.Find(&e).Error
	if err != nil {
		err = fmt.Errorf("%s%s", util.Convert2(), err.Error())
	}
	return e, err
}

/**
* @Author:admin
* @Description: 根据IDS  查询出赛事
* @Param :
* @Return :
* @Date: 11:13 2022/6/21
 */
func GetMatchListByParam(ids []int) ([]*pb.ItemExpertPlan, error) {
	e := make([]*pb.ItemExpertPlan, 0)
	err := global.Sport().Raw(fmt.Sprintf("select tb2.match_id,tb2.match_time,tb2.match_time-unix_timestamp() count_down,ifnull(tb2.comp_short_zh,tb2.comp) comp,tb2.home,tb2.away,tb2.home_score,tb2.away_score,tb2.match_status, "+
		" tb2.id,tb2.home_logo,tb2.away_logo,sport_id from db_matches_schedule tb2 where match_id in(%s)", util.QuerySlice2Str(ids))).Find(&e).Error
	if err != nil {
		err = fmt.Errorf("%s%s", util.Convert2(), err.Error())
	}
	return e, err
}

/**
* @Author:admin
* @Description: 根据会员id,和有料ID 查询购买记录
* @Param :
* @Return :
* @Date: 16:02 2022/6/17
 */
func CountUserPay(userId, planId int64) (counts int32, err error) {
	err = global.GetR().Raw("select count(1) from db_expert_plan_record where plan_id=? and user_id=?", planId, userId).Scan(&counts).Error
	if err != nil {
		err = fmt.Errorf("%s%s", util.Convert2(), err.Error())
		return 0, err
	}
	return
}

/**
* @Author:admin
* @Description: 根据有料ID查询一条有料信息
* @Param :
* @Return :
* @Date: 16:02 2022/6/17
 */
func GetExpertPlanItemByParam(id int32, userId int64) (*pb.ItemExpertPlan, error) {
	e := &pb.ItemExpertPlan{}
	sql := "SELECT tb1.id,tb1.expert_id,tb1.play_type,tb1.title,tb1.fee_type,tb1.fee_pay,tb1.content,tb1.is_win,tb1.home_score plan_home_score,tb1.away_score plan_away_score,tb1.create_time," +
		"tb1.match_id,tb3.`profile`,tb3.nick_name,tb3.is_logicians,case when tb3.plan_done_count>=10 then tb3.win_rate*10 else round(tb3.win_rate/tb3.plan_done_count*100,0) end win_rate,tb3.grade,tb3.resume,tb3.user_id from db_expert_plan tb1,db_expert tb3 WHERE tb1.expert_id=tb3.id and tb1.id=?"
	if userId > 0 {
		sql = fmt.Sprintf("select a1.*,ifnull(a2.is_attention,0)is_attention from (SELECT tb1.id,tb1.expert_id,tb1.play_type,tb1.title,tb1.fee_type,tb1.fee_pay,"+
			"tb1.content,tb1.is_win,tb1.home_score plan_home_score,tb1.away_score plan_away_score,tb1.create_time,tb1.match_id,tb3.`profile`,tb3.nick_name,tb3.is_logicians,"+
			"case when tb3.plan_done_count>=10 then tb3.win_rate*10 else round(tb3.win_rate/tb3.plan_done_count*100,0) end win_rate,tb3.grade,tb3.resume,tb3.user_id from db_expert_plan tb1,db_expert tb3 WHERE tb1.expert_id=tb3.id and  tb1.id=?)a1 left join "+
			"(select 1 is_attention,expert_id from db_expert_attention where user_id=%d) a2 on a1.expert_id=a2.expert_id", userId)
	}
	table := global.GetR().Raw(sql, id)
	err := table.Find(&e).Error
	if err != nil || table.RowsAffected == 0 {
		err = fmt.Errorf("%s%s", util.Convert2(), err.Error())
	}
	return e, err
}

/**
* @Author:admin
* @Description: 根据plan_id 查询当前玩法
* @Param :
* @Return :
* @Date: 9:17 2022/6/18
 */
func GetExpertPlayListByPlanId(planId int64) ([]*pb.ExpertPlay, error) {
	list := make([]*pb.ExpertPlay, 0)
	err := global.GetR().Raw("select * from db_expert_play where expert_plan_id=? order by id", planId).Find(&list).Error
	if errors.Is(err, gorm.ErrRecordNotFound) {
		err = fmt.Errorf("%s%s", "没有找到需要的记录", err.Error())
	}
	if err != nil {
		err = fmt.Errorf("%s%s", util.Convert2(), err.Error())
	}
	return list, err
}

/**
* @Author:admin
* @Description: 插入一条购买记录
* @Param :
* @Return :
* @Date: 9:17 2022/6/18
 */
func InsertExpertPayRecord(session *gorm.DB, expertPayRecordModel *models_main.ExpertPayRecord) error {
	return session.Create(expertPayRecordModel).Error
}

/**
* @Author:admin
* @Description: 根据id 查询出当前有料信息
* @Param :
* @Return :
* @Date: 9:18 2022/6/18
 */
func QueryExpertPlanFee(planId int32) (*models_main.ExpertPlan, error) {
	e := &models_main.ExpertPlan{}
	err := global.GetR().Raw("select tb1.*,tb2.user_id from db_expert_plan tb1,db_expert tb2  where tb1.expert_id=tb2.id and tb1.id=? ", planId).Scan(&e).Error
	if err != nil {
		err = fmt.Errorf("%s%s", util.Convert2(), err.Error())
		return nil, err
	}
	return e, nil
}

/**
* @Author:admin
* @Description: 根据赛事ID 查询这一场赛事开始时间
* @Param :
* @Return :
* @Date: 10:53 2022/6/21
 */
func QueryMatchesMatchTime(matchId int) (matchTime int64) {
	global.Sport().Raw("select match_time from db_matches_schedule where id=?", matchId).Scan(&matchTime)
	return
}

/**
* @Author:admin
* @Description: 根据id 查询出当前有料信息
* @Param :
* @Return :
* @Date: 9:18 2022/6/18
 */
func QueryExpertPlanFeeList(currentTime int64) ([]*models_main.ExpertPlan, error) {
	list := make([]*models_main.ExpertPlan, 0)
	err := global.GetR().Raw("SELECT id,expert_id,is_win FROM `db_expert_plan` where count_done=0 and is_win<>0  and awards_time<? order by create_time asc", currentTime).Scan(&list).Error
	if err != nil {
		err = fmt.Errorf("%s%s", util.Convert2(), err.Error())
		return nil, err
	}
	return list, nil
}

/**
* @Author:admin
* @Description: 更新专家竞猜
* @Param :
* @Return :
* @Date: 18:05 2022/6/18
 */
func UpdateExpertPlan(id int64, session *gorm.DB) int64 {
	return session.Exec("update db_expert_plan set count_done=1 where id=? and count_done=0", id).RowsAffected
}

/**
* @Author:admin
* @Description: 更新专家合计
* @Param :
* @Return :
* @Date: 18:05 2022/6/18
 */
func UpdateExpertSummary(id int64, isWin int32, session *gorm.DB) int64 {
	//fmt.Println("iswin %d____%d",isWin,id)
	return session.Exec("update db_expert set lost_times=lost_times+CASE WHEN 1=? then 1 else 0 end,win_times=win_times+CASE WHEN 2=? then 1 else 0 end,"+
		"last_win=?,done_times=done_times+1 where id=?", isWin, isWin, isWin, id).RowsAffected
}

/**
* @Author:admin
* @Description:有料菜单查询
* @Param :
* @Return :
* @Date: 19:09 2022/6/29
 */
func GetCompListByExpert() ([]*pb.ExpertComp, error) {
	list := make([]*pb.ExpertComp, 0)
	err := global.GetR().Raw("select t1.sport_name, t1.sport_id, GROUP_CONCAT(t2.comp) comps,GROUP_CONCAT(t2.comp_short_zh) comp_short_zhs from db_sports_name t1 " +
		"left join (select  DISTINCT comp,IFNULL(comp_short_zh,comp) comp_short_zh,sport_id from db_expert_plan) t2 on t1.sport_id=t2.sport_id GROUP BY t1.sport_name, t1.sport_id order by t1.serial").Scan(&list).Error
	if err != nil {
		err = fmt.Errorf("%s%s", util.Convert2(), err.Error())
		return nil, err
	}
	return list, nil
}

/**
* @Author:admin
* @Description:根据match_id 查询有料信息 及列表
* @Param :
* @Return :
* @Date: 11:07 2022/6/30
 */
func GetExpertFromMatch(matchResp *pb.ExpertFromMatchReq) ([]*pb.ItemExpertPlan, error) {
	list := make([]*pb.ItemExpertPlan, 0)
	err := global.GetR().Raw("SELECT tb1.id,tb1.expert_id,tb1.play_type,tb1.title,tb1.fee_type,tb1.fee_pay,tb1.content,tb1.is_win,tb1.create_time,tb1.match_id,tb3.`profile`,"+
		"tb3.nick_name,tb3.is_logicians,tb3.grade,case when tb3.plan_done_count>=10 then tb3.win_rate*10 else round(tb3.win_rate/tb3.plan_done_count*100,0) end win_rate,case tb1.match_id when ? then 99 else 0 end orders FROM db_expert_plan tb1,db_expert tb3 WHERE tb1.expert_id = tb3.id and tb1.sport_id=? "+
		"ORDER BY orders desc,tb1.id DESC LIMIT ? OFFSET ?", matchResp.MatchId, matchResp.SportId, matchResp.Size, (matchResp.Page-1)*matchResp.Size).Scan(&list).Error
	if err != nil {
		err = fmt.Errorf("%s%s", util.Convert2(), err.Error())
		return nil, err
	}
	return list, nil
}

func GetHotComp() (compList []*pb.HotCompLabel, err error) {
	err = global.Sport().Raw("select * from db_comp_label order by sport_id,serial").Find(&compList).Error
	return compList, err
}

func GetMatchLikeSearch(req *pb.MatchLikeSearchReq) (compList []*pb.MatchLikeSearch, err error) {
	time := timehelper.DateSkewing(-10)
	//,t1.match_id,t2.home,t2.away,t1.sport_id
	err = global.Sport().Raw(fmt.Sprintf("select DISTINCT case when  instr(upper(t2.home),'%s')>0 then t2.home else t2.away end comp from main.db_expert_plan t1,models_sports.db_matches_schedule t2 where t1.match_id=t2.match_id  "+
		"and (upper(t2.home) like '%%%s%%' or upper(t2.away) like '%%%s%%') and t1.create_time>%d", strings.ToUpper(req.MatchName), strings.ToUpper(req.MatchName), strings.ToUpper(req.MatchName), time)).Find(&compList).Error
	return compList, err
}

func GetExpertList(req *pb.ExpertSearchCond) (list []*pb.ExpertInfo, err error) {
	sql := "select t1.*,0 is_attention  from db_expert t1  "
	if req.UserId > 0 {
		sql = fmt.Sprintf("select t1.*,ifnull(t2.is_attention,0)is_attention from db_expert t1 left join (select expert_id,1 is_attention,user_id from db_expert_attention where user_id=%d)t2 on t1.id=t2.expert_id ", req.UserId)
	}
	//当数量为9的时候是首页查询，其它类型数量跟随名家数量一致
	if req.Size == 9 && req.Cond != _const.EXPERT_LOGICIANS {
		req.Size = int32(countLogiciansCount())
		if req.Size > 9 {
			req.Size = 9
		}
	}
	switch req.Cond {
	case _const.EXPERT_LOGICIANS:
		sql = fmt.Sprintf("%s where t1.is_logicians=1 order by t1.create_time desc limit ? offset ?", sql)
	case _const.EXPERT_WIN:
		sql = fmt.Sprintf("%s  order by case when t1.plan_done_count>=10 then t1.win_rate*10 else round(t1.win_rate/t1.plan_done_count*100,0) end  desc  limit ? offset ?", sql)
	case _const.EXPERT_PROFIT:
		sql = fmt.Sprintf("%s  order by t1.profit_rate desc  limit ? offset ?", sql)
	case _const.EXPERT_SERIAL:
		sql = fmt.Sprintf("%s  order by t1.serial_win desc  limit ? offset ?", sql)
	case _const.EXPERT_ATTENTION:
		sql = fmt.Sprintf("%s where t2.user_id=%d order by t1.attention_count desc  limit ? offset ?", sql, req.UserId)
	}
	err = global.GetR().Raw(sql, req.Size, (req.Page-1)*req.Size).Find(&list).Error
	return list, err
}

func GetExpertById(req *pb.ExpertByIdReq) (info *pb.ExpertInfo, err error) {
	err = global.GetR().Raw("select * from db_expert where id=?", req.ExpertId).Find(&info).Error
	return info, err
}

func GetExpertByIdAndAttention(req *pb.ExpertByIdReq) (info *pb.ExpertInfo, err error) {
	sql := "select id,profile,nick_name,grade,user_id,attention_count,fans_count,case when plan_done_count>=10 then win_rate*10 else round(win_rate/plan_done_count*100,0) end  win_rate,profit_rate,serial_win,resume,0 is_attention from db_expert  where id=?"
	if req.UserId >= 0 {
		sql = fmt.Sprintf("select t1.id,t1.profile,t1.nick_name,t1.grade,t1.user_id,t1.attention_count,t1.fans_count,case when t1.plan_done_count>=10 then t1.win_rate*10 else round(t1.win_rate/t1.plan_done_count*100,0) end win_rate,t1.profit_rate,t1.serial_win,"+
			"t1.resume,t2.is_attention from db_expert t1 left join (select 1 is_attention,expert_id from db_expert_attention where user_id=%d )t2  on t1.id=t2.expert_id  where t1.id=?", req.UserId)
	}
	err = global.GetR().Raw(sql, req.ExpertId).Find(&info).Error
	return info, err
}

/**
 * @Author admin
 * @Description //增加一条专家关注
 * @version: v1.8.0
 * @Date 10:51 2022/7/25
 * @Param
 * @return
 **/
func InsertAttentionExpert(session *gorm.DB, attention *models_main.ExpertAttention) int64 {
	return session.Model(&models_main.ExpertAttention{}).Create(attention).RowsAffected
}

/**
 * @Author admin
 * @Description //删除一条关注
 * @version: v1.8.0
 * @Date 10:53 2022/7/25
 * @Param
 * @return
 **/
func DelAttentionExpert(session *gorm.DB, req *pb.AttentionExpertReq) int64 {
	return session.Where("expert_id=? and user_id=?", req.ExpertId, req.UserId).Delete(&models_main.ExpertAttention{}).RowsAffected
}

/**
 * @Author admin
 * @Description //查询会员是否关注专家
 * @version: v1.8.0
 * @Date 9:57 2022/7/26
 * @Param
 * @return
 **/
func CountAttentionExpertByUserAndExpert(session *gorm.DB, userId, expertId int64) (int64, error) {
	var count int64
	err := session.Model(&models_main.ExpertAttention{}).Where("expert_id=? and user_id=?", expertId, userId).Count(&count).Error
	return count, err
}

/**
 * @Author admin
 * @Description //查询当前会员是否是专家
 * @version: v1.8.0
 * @Date 10:55 2022/7/25
 * @Param
 * @return
 **/
func CountAttentionExpert(session *gorm.DB, userId int) (int64, error) {
	var count int64
	err := session.Model(&models_main.Expert{}).Where("user_id", userId).Count(&count).Error
	return count, err
}

/**
 * @Author admin
 * @Description //合计是否有专家
 * @version: v1.8.0
 * @Date 10:55 2022/7/25
 * @Param
 * @return
 **/
func CountExpert(expertId int) int64 {
	var count int64
	global.GetR().Model(&models_main.Expert{}).Where("id", expertId).Count(&count)
	return count
}

/**
 * @Author admin
 * @Description //更新粉丝数量
 * @version: v1.8.0
 * @Date 11:09 2022/7/25
 * @Param
 * @return
 **/
func UpdateAttentionFansExpert(session *gorm.DB, expertId, quantity int) int64 {
	return session.Exec("update db_expert set fans_count=ifnull(fans_count,0)+? where id=?", quantity, expertId).RowsAffected
}

/**
 * @Author admin
 * @Description //更新当前会员如果是专家关注的人员
 * @version: v1.8.0
 * @Date 11:09 2022/7/25
 * @Param
 * @return
 **/
func UpdateAttentionExpert(session *gorm.DB, userId, quantity int) int64 {
	return session.Exec("update db_expert set attention_count=ifnull(attention_count,0)+? where user_id=?", quantity, userId).RowsAffected
}

/**
 * @Author admin
 * @Description //合这绝
 * @version: v1.8.0
 * @Date 10:55 2022/7/25
 * @Param
 * @return
 **/
func CountAttention(userId, expertId int) int64 {
	var count int64
	global.GetR().Model(&models_main.ExpertAttention{}).Where("user_id=? and expert_id=?", userId, expertId).Count(&count)
	return count
}

func countLogiciansCount() int64 {
	var count int64
	global.GetR().Model(&models_main.Expert{}).Where("is_logicians=1").Count(&count)
	return count
}
